package org.bravof.differ.service.support;

import lombok.extern.log4j.Log4j;
import org.bravof.differ.dao.DetailDAO;
import org.bravof.differ.entity.DetailPO;
import org.bravof.differ.entity.dbbase.SchemaBody;
import org.bravof.differ.entity.dbbase.TableBody;
import org.bravof.differ.entity.dbbase.TableField;
import org.bravof.differ.util.CommonConst;
import org.bravof.differ.util.FieldUtil;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Objects;

/**
 * 数据库表结构查询服务
 */
@Log4j
@Service
public class DBQueryService {
    @Resource
    private ConnectionService connectionService;
    @Resource
    private DetailDAO detailDAO;


    /**
     * 查询并生成 SchemaBody 实例
     *
     * @param id
     * @return
     */
    public SchemaBody getSchemaStructById(String id) {
        Connection conn = connectionService.fetchConnection(id);
        if (null == conn) {
            log.warn("获取连接失败，连接ID -> " + id);
        }
        DetailPO detail = detailDAO.findDetailById(id);
        if (null == detail) {
            log.warn("获取库配置失败，配置ID -> " + id);
        }

        SchemaBody schemaBody = new SchemaBody();
        schemaBody.setSchemaName(detail.getSchemaName());

        try {
            ResultSet rs = commonQuery(conn, CommonConst.MYSQL.QRY_SHOW_TABLES);
            // 获取数据库下所有表名
            List<String> tableNameList = obtainTableNameList(rs);
            // 遍历数据库下所有表的结构
            LinkedHashMap<String, TableBody> tableNameAndTableBodyMap = new LinkedHashMap<>();
            for (String tableName : tableNameList) {
                ResultSet rs2 = commonQuery(conn, CommonConst.MYSQL.QRY_DESCRIBE + tableName);
                LinkedHashMap<String, TableField> fieldLinkedHashMap = obtainTableBody(rs2);
                // 构造表模型实体
                TableBody body = new TableBody();
                body.setTableName(tableName);
                body.setFieldMap(fieldLinkedHashMap);
                tableNameAndTableBodyMap.put(tableName, body);
            }
            schemaBody.setTableMap(tableNameAndTableBodyMap);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return schemaBody;
    }

    /**
     * 获取单个表的整个建表sql语句
     * @param id
     * @param tableName
     * @return
     */
    public String generateWholeTableSql(String id, String tableName) {
        Connection conn = connectionService.fetchConnection(id);
        if (null == conn) {
            log.warn("获取连接失败，连接ID -> " + id);
        }

        try {
            ResultSet rs = commonQuery(conn, CommonConst.MYSQL.QRY_SHOW_CREATE_TABLE + tableName);
            while (rs.next()) {
                return rs.getString(2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 根据结果集获取单表内所有字段模型
     * @param rs
     * @return
     * @throws SQLException
     */
    private LinkedHashMap<String, TableField> obtainTableBody(ResultSet rs) throws SQLException {
        LinkedHashMap<String, TableField> fieldMap = new LinkedHashMap<>();
        while (rs.next()) {
            TableField field = new TableField();
            String rsField = rs.getString(1);
            String rsType = rs.getString(2);
            String rsNull = rs.getString(4);
            String rsKey = rs.getString(5);
            String rsDefault = rs.getString(6);
            String rsExtra = rs.getString(7);
            String rsComment = rs.getString(9);
            // 转化值
            field.setName(rsField);
            FieldUtil.handleTableField4Type(field, rsType);
            field.setDefaultValue(rsDefault);
            field.setComment(rsComment);
            FieldUtil.handleTableField4Null(field, rsNull);
            FieldUtil.handleTableField4AutoIncr(field, rsExtra);

            fieldMap.put(rsField, field);
        }

        return fieldMap;
    }

    /**
     * 获取数据库表名列表的私有处理方法
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    private List<String> obtainTableNameList(ResultSet rs) throws SQLException {
        List<String> list = new ArrayList<>();
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        return list;
    }

    /**
     * 通用查询方法，返回结果集
     *
     * @param conn
     * @param sql
     * @return ResultSet
     * @throws SQLException
     */
    private ResultSet commonQuery(Connection conn, String sql) throws SQLException {
        if (null == conn) {
            log.warn("数据库连接失效");
        }
        log.debug("执行sql -> " + sql);
        Statement stmt = Objects.requireNonNull(conn).createStatement();
        return stmt.executeQuery(sql);
    }
}
